package com.example.sqlitedatabasedemo;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    Button creatBtn, deleteDB,queryBtn,deleteItemBtn,insertBtn;
    TextView txt;
    DatabaseHelper mOpenHelper;
    MySQLDatabase myc;
    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        creatBtn=(Button)findViewById(R.id.createdb);
        creatBtn.setOnClickListener(new mClick());
        deleteDB=(Button)findViewById(R.id.deleteDB);
        deleteDB.setOnClickListener(new mClick());
        insertBtn=(Button)findViewById(R.id.insertdb);
        insertBtn.setOnClickListener(new mClick());
        deleteItemBtn=(Button)findViewById(R.id.deleteItemBtn);
        deleteItemBtn.setOnClickListener(new mClick());
        queryBtn=(Button)findViewById(R.id.querydb);
        queryBtn.setOnClickListener(new mClick());
        txt=(TextView)findViewById(R.id.txt);
        mOpenHelper = new DatabaseHelper(MainActivity.this);
    }

    class mClick implements View.OnClickListener
    {
        @Override
        public void onClick(View arg0)
        {
            if(arg0 == creatBtn)
            {
                myc =  new MySQLDatabase();
                myc.onCreate();
                Toast.makeText(MainActivity.this, "数据库和数据表创建成功", Toast.LENGTH_SHORT).show();
            }
            else if(arg0 == deleteDB)
            {
                deleteDatabase(Database_name);
                Toast.makeText(MainActivity.this, "数据库删除成功", Toast.LENGTH_SHORT).show();
            }
            else if(arg0 == queryBtn)
            {
                myc.showItems();
                Toast.makeText(MainActivity.this, "数据查询成功", Toast.LENGTH_SHORT).show();
            }
            else if(arg0 == insertBtn)
            {
                myc.insertItem();
                Log.i("sql=", "插入两条数据");
                Toast.makeText(MainActivity.this, "数据插入成功", Toast.LENGTH_SHORT).show();
            }
            else if(arg0 == deleteItemBtn)
            {
                myc.deleteItem();
                Toast.makeText(MainActivity.this, "删除数据成功", Toast.LENGTH_SHORT).show();
            }
        }
    }

    static final String Database_name = "eBook.db";//数据库名称
    static final int Database_Version = 1;
    static final String TABLE_NAME = "diary";//数据表名称，日记
    static final String ID = "nid";    //ID编号，数据表的主键
    static final String TITLE = "title";   //标题
    static final String BODY = "body"; //正文

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, Database_name, null, Database_Version);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {}
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
    }
    class MySQLDatabase
    {
        SQLiteDatabase db;
        public void onCreate() {
            String DATABASE_CREATE =
                    "CREATE TABLE " + TABLE_NAME + " ("
                            + ID  + " INTEGER primary key autoincrement,"//ID自动编号
                            + TITLE + " text not null, "
                            + BODY + " text not null);";
            int mode = MODE_PRIVATE;
            try {
                db = openOrCreateDatabase(Database_name, mode, null);//创建数据库
                db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);//查看数据表，如存在就删除
                db.execSQL(DATABASE_CREATE);//创建数据表
                txt.setText("数据库重建成功");
            } catch (SQLException e) {
                txt.setText("数据库重建错误");
            }
        }
        /*
         * 插入两条数据
         */
        private void insertItem() {
            try {
                ContentValues values = new ContentValues();
                values.put(TITLE, "Android");
                values.put(BODY,"发展真是迅速啊");
                db.insert(TABLE_NAME,ID,values);

                values.put(TITLE, "城市");
                values.put(BODY,"发展真是迅速啊");
                db.insert(TABLE_NAME,ID,values);

                txt.setText("插入两条数据成功");
            } catch (SQLException e) {
                txt.setText("插入两条数据失败");
            }
        }
        /*
         * 删除符合条件的数据
         */
        private void deleteItem() {
            try {
                db.delete(TABLE_NAME, " TITLE = '城市'", null);
                txt.setText("删除城市的记录");
            } catch (SQLException e) {
                txt.setText("数据记录删除错误");
            }
        }
        /*
         * 显示当前数据表的记录数
         */
        private void showItems() {
            String col[] = { TITLE, BODY };
            Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);
            Integer num = cur.getCount();
            String s="";
            if(num>0){
                cur.moveToFirst();
                s = "第一条记录：\n"+cur.getString(0)+cur.getString(1);

            }
            txt.setText("共有"+Integer.toString(num) + " 条记录\n"+s);
        }
    }
}